使用sqlloader的直接加载方式和传统加载方式的性能差异

您所在的位置：网站首页 › sqlloader 日志 › 使用sqlloader的直接加载方式和传统加载方式的性能差异

使用sqlloader的直接加载方式和传统加载方式的性能差异

#使用sqlloader的直接加载方式和传统加载方式的性能差异| 来源: 网络整理| 查看: 265

Direct 直接加载特点（1）数据绕过SGA直接写入磁盘的数据文件（2）数据直接写入高水位线HWM之后的新块，不会扫描HWM之前的空闲块（3）commit之后移动HWM他人才能看到（4）不对已用空间进行扫描（5）使用direct几乎不产生redo log，不是完全不产生(安全性差)，但会产生undo数据（6）适用OLAP在线分析场景，增删改不频繁的场景

Conventional传统加载特点（1）数据先加载 -> SGA -> 磁盘的数据文件（2）会扫描高水位线HWM之前的数据块，如果有空闲块(碎片经常DML导致)就会利用，如果没有再插入新块（3）高水位线HWM之前的数据块是放在SGA区的（4）会产生redo log和undo数据（5）安全性高，可恢复数据（6）传统加载与SQL语句insert插入没区别

1.创建一个文本数据

BYS@ bys001>set termout off;

BYS@ bys001>set feedback off;

BYS@ bys001>set echo off;

BYS@ bys001>set heading off;

BYS@ bys001>set trimout on;

BYS@ bys001>set trimspool on;

BYS@ bys001>spool /home/oracle/sqlload.txt

BYS@ bys001>select owner||','||object_name||','||object_id from dba_objects;

BYS@ bys001>spool off;

此时退出SQLPLUS,到/home/oracle/ 下查看

[oracle@oel-01 ~]$ tail sqlload.txt

SCOTT,TEST_AUDIT,75940

SYS,WRH$_ACTIVE_SESSION_HISTORY,76484

SYS,WRH$_TABLESPACE_STAT,76488

BYS@ bys001>spool off;

[oracle@oel-01 ~]$ vi sqlload.txt

输入 shift + g 将光标定位到最后一行，删除BYS@ bys001>spool off; 这一行，保存退出。

[oracle@oel-01 ~]$ cat sqlload.txt | wc -l

72893

2.数据库中创建表，OS中创建SQL*LOADER的控制文件

BYS@ bys001>create table test1(owner varchar2(30),object_name varchar2(150),object_id number);

Table created.

创建控制文件，内容如下

[oracle@oel-01 ~]$ cat sqlload.ctl

load data

infile '/home/oracle/sqlload.txt'

badfile '/home/oracle/sqlload_bad.txt'

discardfile '/home/oracle/sqlload_discard.txt'

append into table test1

fields terminated by ","

trailing nullcols

(owner,object_name,object_id)

3.开始加载，使用直接加载方法，用时约1秒

[oracle@oel-01 ~]$ sqlldr bys/bys control=sqlload.ctl log=sqlload.log direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Jul 29 10:10:06 2013

Load completed - logical record count 72893.

日志如下：

[oracle@oel-01 ~]$ cat sqlload.log

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Jul 29 10:10:06 2013

Control File: sqlload.ctl

Data File: /home/oracle/sqlload.txt

Bad File: /home/oracle/sqlload_bad.txt

Discard File: /home/oracle/sqlload_discard.txt

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Continuation: none specified

Path used: Direct

Table TEST1, loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

OWNER FIRST * , CHARACTER

OBJECT_NAME NEXT * , CHARACTER

OBJECT_ID NEXT * , CHARACTER

Record 2: Discarded - all columns null.

Record 1: Rejected - Error on table TEST1, column OBJECT_ID.

ORA-01722: invalid number

Record 10001: Discarded - all columns null.

Record 20000: Discarded - all columns null.

Record 29999: Discarded - all columns null.

Record 39998: Discarded - all columns null.

Record 49997: Discarded - all columns null.

Record 59996: Discarded - all columns null.

Record 69995: Discarded - all columns null.

Table TEST1:

72884 Rows successfully loaded.

1 Row not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

8 Rows not loaded because all fields were null.

Bind array size not used in direct path.

Column array rows : 5000

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 72893

Total logical records rejected: 1

Total logical records discarded: 8

Total stream buffers loaded by SQL*Loader main thread: 16

Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Mon Jul 29 10:10:06 2013

Run ended on Mon Jul 29 10:10:07 2013

Elapsed time was: 00:00:00.99

CPU time was: 00:00:00.12

用时约1秒

4.使用传统加载方法：用时3秒

[oracle@oel-01 ~]$ echo 1>sqlload.log

在SQLPLUS中删除表中数据。

BYS@ bys001>select count(*) from test1;

COUNT(*)

----------

72884

BYS@ bys001>truncate table test1;

[oracle@oel-01 ~]$ sqlldr bys/bys control=sqlload.ctl log=sqlload.log

在SQLPLUS中查看：